using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    /// <summary> 
    ///Author: daiduong19051986@gmail.com
    /// <summary>
    public class RuleSetDataAccess : Connection
    {
        #region Local Variable
      
        private string _strTableName = "[RuleSetRV]";
        private string _strSPGetTransferOutName = "dbo.[procRuleSetRV_gettransferout]";
        #endregion Local Variable

        #region Method
        public RuleSetInfo Get(string db, Int32 AUTO_ID, String RULESET_CODE, ref string sErr)
        {
            string sql = @"SELECT        AUTO_ID, RULESET_CODE, COMMAND, FIELD, START, LENGTH, OPERATOR, VALUE, COL0, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9
            FROM            RuleSet" + db + @"
            WHERE        (AUTO_ID = @AUTO_ID) AND (RULESET_CODE = @RULESET_CODE)";
            RuleSetInfo objEntr = new RuleSetInfo();
            InitConnect();
            InitCommand(sql);
            AddParameter(RuleSetInfo.Field.AUTO_ID.ToString(), AUTO_ID);
            AddParameter(RuleSetInfo.Field.RULESET_CODE.ToString(), RULESET_CODE);

            DataTable list = new DataTable();
            try { list = executeSelect(); }
            catch (Exception ex) { sErr = ex.Message; }
            finally { closeConnection(); }

            if (list.Rows.Count > 0)
                objEntr = new RuleSetInfo(list.Rows[0]);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }

        public DataTable GetAll(string db, ref string sErr)
        {
            string sql = @"SELECT        AUTO_ID, RULESET_CODE, COMMAND, FIELD, START, LENGTH, OPERATOR, VALUE, COL0, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9
            FROM            RuleSet" + db;
            InitConnect();
            InitCommand(sql);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        /// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add(string db, RuleSetInfo objEntr, ref string sErr)
        {
            string sql = @"INSERT INTO RuleSet" + db + @"
                         (AUTO_ID, RULESET_CODE, COMMAND, FIELD, START, LENGTH, OPERATOR, VALUE, COL0, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9)
            VALUES        (@AUTO_ID,@RULESET_CODE,@COMMAND,@FIELD,@START,@LENGTH,@OPERATOR,@VALUE,@COL0,@COL1,@COL2,@COL3,@COL4,@COL5,@COL6,@COL7,@COL8,@COL9)
            SELECT SCOPE_IDENTITY()";
            int ret = -1;
            openConnection();
            InitCommand(sql);
            AddParameter(RuleSetInfo.Field.AUTO_ID.ToString(), objEntr.AUTO_ID);
            AddParameter(RuleSetInfo.Field.RULESET_CODE.ToString(), objEntr.RULESET_CODE);
            AddParameter(RuleSetInfo.Field.COMMAND.ToString(), objEntr.COMMAND);
            AddParameter(RuleSetInfo.Field.FIELD.ToString(), objEntr.FIELD);
            if (objEntr.START != -1)
                AddParameter(RuleSetInfo.Field.START.ToString(), objEntr.START);
            else AddParameter(RuleSetInfo.Field.START.ToString(), DBNull.Value);
            if (objEntr.LENGTH != -1)
                AddParameter(RuleSetInfo.Field.LENGTH.ToString(), objEntr.LENGTH);
            else AddParameter(RuleSetInfo.Field.LENGTH.ToString(), DBNull.Value);

            AddParameter(RuleSetInfo.Field.OPERATOR.ToString(), objEntr.OPERATOR);
            AddParameter(RuleSetInfo.Field.VALUE.ToString(), objEntr.VALUE);
            AddParameter(RuleSetInfo.Field.COL0.ToString(), objEntr.COL0);
            AddParameter(RuleSetInfo.Field.COL1.ToString(), objEntr.COL1);
            AddParameter(RuleSetInfo.Field.COL2.ToString(), objEntr.COL2);
            AddParameter(RuleSetInfo.Field.COL3.ToString(), objEntr.COL3);
            AddParameter(RuleSetInfo.Field.COL4.ToString(), objEntr.COL4);
            AddParameter(RuleSetInfo.Field.COL5.ToString(), objEntr.COL5);
            AddParameter(RuleSetInfo.Field.COL6.ToString(), objEntr.COL6);
            AddParameter(RuleSetInfo.Field.COL7.ToString(), objEntr.COL7);
            AddParameter(RuleSetInfo.Field.COL8.ToString(), objEntr.COL8);
            AddParameter(RuleSetInfo.Field.COL9.ToString(), objEntr.COL9);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public string Update(string db, RuleSetInfo objEntr)
        {
            string sql = @"UPDATE       RuleSet" + db + @"
            SET                COMMAND = @COMMAND, FIELD = @FIELD, START = @START, LENGTH = @LENGTH, OPERATOR = @OPERATOR, VALUE = @VALUE, COL0 = @COL0, COL1 = @COL1, COL2 = @COL2, COL3 = @COL3, 
                         COL4 = @COL4, COL5 = @COL5, COL6 = @COL6, COL7 = @COL7, COL8 = @COL8, COL9 = @COL9
            WHERE        (AUTO_ID = @AUTO_ID) AND (RULESET_CODE = @RULESET_CODE)";
            openConnection();
            InitCommand(sql);
            AddParameter(RuleSetInfo.Field.AUTO_ID.ToString(), objEntr.AUTO_ID);
            AddParameter(RuleSetInfo.Field.RULESET_CODE.ToString(), objEntr.RULESET_CODE);
            AddParameter(RuleSetInfo.Field.COMMAND.ToString(), objEntr.COMMAND);
            AddParameter(RuleSetInfo.Field.FIELD.ToString(), objEntr.FIELD);
            if (objEntr.START != -1)
                AddParameter(RuleSetInfo.Field.START.ToString(), objEntr.START);
            else AddParameter(RuleSetInfo.Field.START.ToString(), DBNull.Value);
            if (objEntr.LENGTH != -1)
                AddParameter(RuleSetInfo.Field.LENGTH.ToString(), objEntr.LENGTH);
            else AddParameter(RuleSetInfo.Field.LENGTH.ToString(), DBNull.Value);
            AddParameter(RuleSetInfo.Field.OPERATOR.ToString(), objEntr.OPERATOR);
            AddParameter(RuleSetInfo.Field.VALUE.ToString(), objEntr.VALUE);
            AddParameter(RuleSetInfo.Field.COL0.ToString(), objEntr.COL0);
            AddParameter(RuleSetInfo.Field.COL1.ToString(), objEntr.COL1);
            AddParameter(RuleSetInfo.Field.COL2.ToString(), objEntr.COL2);
            AddParameter(RuleSetInfo.Field.COL3.ToString(), objEntr.COL3);
            AddParameter(RuleSetInfo.Field.COL4.ToString(), objEntr.COL4);
            AddParameter(RuleSetInfo.Field.COL5.ToString(), objEntr.COL5);
            AddParameter(RuleSetInfo.Field.COL6.ToString(), objEntr.COL6);
            AddParameter(RuleSetInfo.Field.COL7.ToString(), objEntr.COL7);
            AddParameter(RuleSetInfo.Field.COL8.ToString(), objEntr.COL8);
            AddParameter(RuleSetInfo.Field.COL9.ToString(), objEntr.COL9);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete(string db, Int32 AUTO_ID, String RULESET_CODE)
        {
            string sql = @"DELETE FROM RuleSet" + db + @"
            WHERE        (RULESET_CODE = @RULESET_CODE)";
            openConnection();
            InitCommand(sql);
            AddParameter(RuleSetInfo.Field.AUTO_ID.ToString(), AUTO_ID);
            AddParameter(RuleSetInfo.Field.RULESET_CODE.ToString(), RULESET_CODE);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public Boolean IsExist(string db, Int32 AUTO_ID, String RULESET_CODE)
        {
            string sql = @"SELECT        TOP (1) AUTO_ID
            FROM            RuleSet" + db + @"
            WHERE        (AUTO_ID = @AUTO_ID) AND (RULESET_CODE = @RULESET_CODE)";
            InitConnect();
            InitCommand(sql);
            AddParameter(RuleSetInfo.Field.AUTO_ID.ToString(), AUTO_ID);
            AddParameter(RuleSetInfo.Field.RULESET_CODE.ToString(), RULESET_CODE);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        private string CreateWhereClause(RuleSetInfo obj)
        {
            String result = "";

            return result;
        }

        public DataTable Search(string columnName, string columnValue, string condition, string tableName, ref string sErr)
        {
            string query = "select * from " + _strTableName + " where " + columnName + " " + condition + " " + columnValue;
            DataTable list = new DataTable();
            openConnection();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetTransferOut(string dtb, object from, object to, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetTransferOutName);
            AddParameter("DB", dtb);
            AddParameter("FROM", from);
            AddParameter("TO", to);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        #endregion Method

        public DataTable GetbyRuleSetCode(string db, string RuleSetCode, ref string sErr)
        {
            string sql = @"SELECT        AUTO_ID, RULESET_CODE, COMMAND, FIELD, START, LENGTH, OPERATOR, VALUE, COL0, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9
            FROM            RuleSet" + db + @"
            WHERE RULESET_CODE = @RULESET_CODE";
            InitConnect();
            InitCommand(sql);
            AddParameter(RuleSetInfo.Field.RULESET_CODE.ToString(), RuleSetCode);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }


        public DataTable GetbyRuleSetCode(string db, string RuleSetCode, string strWhere, ref string sErr)
        {
            string sql = @"SELECT        AUTO_ID, RULESET_CODE, COMMAND, FIELD, START, LENGTH, OPERATOR, VALUE, COL0, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9
            FROM            RuleSet" + db + @"
            WHERE RULESET_CODE = @RULESET_CODE";
            if (!string.IsNullOrEmpty(strWhere)) sql += " AND " + strWhere;
            InitConnect();
            InitCommand(sql);
            AddParameter(RuleSetInfo.Field.RULESET_CODE.ToString(), RuleSetCode);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
    }
}
